package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.data.repositories.FinanceOrderRedPushRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.FinanceWriteOffService;
import org.springframework.stereotype.Repository;

import java.util.Map;

/**
 * Created by admin on 2017/12/15.
 */
@Repository
public class FinanceOrderRedPushRepositoryImpl extends BaseRepository implements FinanceOrderRedPushRepository {

    @Override
    public PageData getFinanceOrderRedPush(String permit,FinanceWriteOffService.FinanceWriteOffRequest request) {
        String sql = "SELECT * FROM (select\n" +
                "a.报价编号,\n" +
                "a.初始报价单号,\n" +
                "e.财务编号,\n" +
                "a.初始报价单号 订单号,\n" +
                "case \n" +
                "when b.订单状态 = '意向订单C' and not isnull(b.订单审批通过日期) and date(b.订单审批通过日期) >= ifnull(b.订单失销日期,'1970-01-01') and b.订金类型 = '意向金'  then '意向订单'\n" +
                "when b.订单状态 = '订单C' and not isnull(b.订单审批通过日期) and date(b.订单审批通过日期) >= ifnull(b.订单失销日期,'1970-01-01') and b.订金类型 = '订金' then '订单'\n" +
                "when b.订单状态 is null or b.订单状态 = '退订C' then '退订单'\n" +
                "else '合同' " +
                "end as 订单类型,\n" +
                "if(locate('红冲',i.账目类型)>0,(select 账目单号 from 销售财务账目表 where 账目编号 = i.关联编号),'') as '来源单据号',\n" +
                "i.账目单号 单据号,\n" +
                "i.账目类型 单据类型,\n" +
                "i.账目日期 单据日期,\n" +
                "c.顾客姓名 客户姓名,\n" +
                "c.电话号码 联系电话,\n" +
                "CASE WHEN a.客户类型='非营业性个人' THEN '个人'\n" +
                "WHEN a.客户类型='公司' THEN '企业' END AS 客户类型,\n" +
                "g.身份证 身份证号码,\n" +
                "d.车架号,\n" +
                "h.`人员姓名` 销售顾问, \n" +
                "i.账目金额 金额,\n" +
                "IF(i.是否红冲='1','是','否') 是否红冲," +
                "CASE when f.`交车日期` is not null then '已交车' ELSE '未交车' end as 交车状态,\n" +
                "i.账目编号, i.交易凭证号,i.备注, DATE_FORMAT(i.修改时间,'%Y-%m-%d') 红冲日期, " +
                "i.操作日期 操作时间\n" +
                "from 报价单表 a\n" +
                "join (select max(报价编号) 报价编号 from 报价单表 where 许可 = :permit group by 初始报价单号) k on a.报价编号 = k.报价编号\n" +
                "left join 报价订单表 b on a.订单编号 = b.订单编号\n" +
                "left join 顾客记录汇总 c on a.顾客编号 = c.顾客编号\n" +
                "left join lsh库存表 d on a.库存编号 = d.库存编号\n" +
                "join 销售财务表 e on a.初始报价单号 = e.`订单编号`\n" +
                "left join 报价交车表 f on a.交车 = f.编号\n" +
                "left join 报价客户表 g on a.客户详细 = g.编号\n" +
                "left join 用户 h on a.销售顾问 = h.用户名\n" +
                "JOIN 销售财务账目表 i ON e.财务编号 = i.财务编号\n" +
                "WHERE i.账目类型 !='发票' AND i.账目类型 !='红冲发票' " +
                " AND e.许可= :permit " +
                ") aa \n" +
                "WHERE 1=1 ";
        Map params = Maps.newHashMap();
        params.put("permit",permit);
        if (!Strings.isNullOrEmpty(request.orderType)){
            sql += " AND aa.订单类型 = :orderType ";
            params.put("orderType",request.orderType);
        }
        if (!Strings.isNullOrEmpty(request.sc)){
            sql += " AND aa.销售顾问 LIKE :sc ";
            params.put("sc","%" + request.sc + "%");
        }
        if (!Strings.isNullOrEmpty(request.carType)){
            sql += " AND aa.交车状态 = :carType ";
            params.put("carType",request.carType);
        }
        if (!Strings.isNullOrEmpty(request.idCardNumber)){
            sql += " AND aa.身份证号码 LIKE :idCardNumber ";
            params.put("idCardNumber","%" + request.idCardNumber + "%");
        }
        if (!Strings.isNullOrEmpty(request.orderId)){
            sql += " AND aa.订单号 like :orderId ";
            params.put("orderId","%" + request.orderId + "%");
        }
        if (!Strings.isNullOrEmpty(request.custName)){
            sql += " AND aa.客户姓名 like :custName ";
            params.put("custName","%" + request.custName + "%");
        }
        if (!Strings.isNullOrEmpty(request.custPhone)){
            sql += " AND aa.联系电话 like :custPhone ";
            params.put("custPhone","%" + request.custPhone + "%");
        }
        if (!Strings.isNullOrEmpty(request.frameNo)){
            sql += " AND aa.车架号 like :frameNo ";
            params.put("frameNo","%" + request.frameNo + "%");
        }
        if (!Strings.isNullOrEmpty(request.documentsType)){
            sql += " AND aa.单据类型 = :documentsType ";
            params.put("documentsType",request.documentsType);
        }
        if (!Strings.isNullOrEmpty(request.documentsId)){
            sql += " AND aa.单据号 like :documentsId ";
            params.put("documentsId","%" + request.documentsId + "%");
        }
        if (!Strings.isNullOrEmpty(request.SourceDocumentsId)){
            sql += " AND aa.来源单据号 like :SourceDocumentsId ";
            params.put("SourceDocumentsId","%" + request.SourceDocumentsId + "%");
        }
        if (!Strings.isNullOrEmpty(request.documentDate)){
            sql += " AND aa.单据日期 = :documentDate ";
            params.put("documentDate",request.documentDate);
        }


        return this.getPage(sql,params,request.getPageSize(),request.getPageIndex());
    }
}
